package com.education.ai.controller;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 管理员统计控制器，提供各类统计数据API
 * 
 * 修复记录：
 * 1. 将student_exercise_records、student_knowledge_mastery和student_error_records表替换为实际存在的student_practice_sessions表
 * 2. 修改字段名称，匹配实际表结构
 * 3. 添加错误处理和空值检查，防止空指针异常
 * 4. 添加除零保护，使用NULLIF函数
 * 5. 使用包含时间的日期格式，确保正确匹配datetime类型字段
 */
@RestController
@RequestMapping("/api/admin/statistics")
@CrossOrigin(origins = {"http://localhost:3000", "http://localhost:8080", "http://localhost:8081"}, allowCredentials = "true")
public class AdminStatisticsController {

    private static final Logger logger = LoggerFactory.getLogger(AdminStatisticsController.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 获取教师使用统计数据
     */
    @GetMapping("/teacher-usage")
    public ResponseEntity<?> getTeacherUsageStats(@RequestParam(defaultValue = "week") String period) {
        try {
            logger.info("获取教师使用统计数据，周期: {}", period);
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            
            Map<String, Object> data = new HashMap<>();
            List<Map<String, Object>> dailyStats = new ArrayList<>();
            List<Map<String, Object>> moduleStats = new ArrayList<>();
            
            // 获取日期范围
            LocalDateTime endDate = LocalDateTime.now();
            LocalDateTime startDate;
            
            switch (period) {
                case "day":
                    startDate = endDate.minusDays(1);
                    break;
                case "week":
                    startDate = endDate.minusWeeks(1);
                    break;
                case "month":
                    startDate = endDate.minusMonths(1);
                    break;
                default:
                    startDate = endDate.minusWeeks(1);
            }
            
            // 格式化日期为SQL格式
            DateTimeFormatter sqlFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String startDateStr = startDate.format(sqlFormatter);
            String endDateStr = endDate.format(sqlFormatter);
            
            // 获取每日登录次数统计 - 基于login_count字段
            String dailyLoginSql = "SELECT DATE(last_login_time) as date, SUM(login_count) as count " +
                    "FROM teachers " +
                    "WHERE DATE(last_login_time) BETWEEN ? AND ? " +
                    "GROUP BY DATE(last_login_time) " +
                    "ORDER BY DATE(last_login_time)";
            
            List<Map<String, Object>> dailyResults = jdbcTemplate.queryForList(dailyLoginSql, startDateStr, endDateStr);
            
            // 如果没有按日期的登录数据，则获取总体登录次数
            if (dailyResults.isEmpty()) {
                // 直接统计全部教师的登录次数分布
                String loginCountSql = "SELECT login_count as count, COUNT(*) as teacher_count FROM teachers GROUP BY login_count ORDER BY login_count";
                List<Map<String, Object>> loginCountData = jdbcTemplate.queryForList(loginCountSql);
                
                LocalDateTime currentDate = startDate;
                int index = 0;
                while (!currentDate.isAfter(endDate)) {
                    Map<String, Object> stat = new HashMap<>();
                    String formattedDate = currentDate.format(sqlFormatter);
                    stat.put("date", formattedDate);
                    
                    // 如果有数据则使用真实数据，否则使用0
                    if (index < loginCountData.size()) {
                        Map<String, Object> row = loginCountData.get(index);
                        // 添加对teacher_count字段的null检查
                        Object countObj = row.get("teacher_count");
                        int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                        stat.put("count", count);
                        index++;
                    } else {
                        stat.put("count", 0);
                    }
                    
                    dailyStats.add(stat);
                    currentDate = currentDate.plusDays(1);
                }
            } else {
                for (Map<String, Object> row : dailyResults) {
                    Map<String, Object> stat = new HashMap<>();
                    // 添加对date字段的null检查
                    Object dateObj = row.get("date");
                    stat.put("date", (dateObj != null) ? dateObj.toString() : "未知日期");
                    
                    // 添加对count字段的null检查
                    Object countObj = row.get("count");
                    int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                    stat.put("count", count);
                    
                    dailyStats.add(stat);
                }
            }
            
            // 查询教师活跃模块分布
            String moduleSql = "SELECT subject as module, SUM(login_count) as count " +
                    "FROM teachers " +
                    "GROUP BY subject " +
                    "ORDER BY count DESC";
            
            List<Map<String, Object>> moduleResults = jdbcTemplate.queryForList(moduleSql);
            
            for (Map<String, Object> row : moduleResults) {
                Map<String, Object> stat = new HashMap<>();
                // 如果科目为null，则显示为"未指定科目"
                Object moduleObj = row.get("module");
                String moduleName = (moduleObj != null) ? moduleObj.toString() : "未指定科目";
                stat.put("module", moduleName);
                
                // 添加对count字段的null检查
                Object countObj = row.get("count");
                int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                stat.put("count", count);
                
                moduleStats.add(stat);
            }
            
            data.put("dailyStats", dailyStats);
            data.put("moduleStats", moduleStats);
            
            result.put("data", data);
            
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            logger.error("获取教师使用统计数据失败: {}", e.getMessage(), e);
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "获取教师使用统计数据失败: " + e.getMessage());
            return ResponseEntity.badRequest().body(response);
        }
    }
    
    /**
     * 获取学生使用统计数据
     */
    @GetMapping("/student-usage")
    public ResponseEntity<?> getStudentUsageStats(@RequestParam(defaultValue = "week") String period) {
        try {
            logger.info("获取学生使用统计数据，周期: {}", period);
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            
            Map<String, Object> data = new HashMap<>();
            List<Map<String, Object>> dailyStats = new ArrayList<>();
            List<Map<String, Object>> moduleStats = new ArrayList<>();
            
            // 获取日期范围
            LocalDateTime endDate = LocalDateTime.now();
            LocalDateTime startDate;
            
            switch (period) {
                case "day":
                    startDate = endDate.minusDays(1);
                    break;
                case "week":
                    startDate = endDate.minusWeeks(1);
                    break;
                case "month":
                    startDate = endDate.minusMonths(1);
                    break;
                default:
                    startDate = endDate.minusWeeks(1);
            }
            
            // 格式化日期为SQL格式
            DateTimeFormatter sqlFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String startDateStr = startDate.format(sqlFormatter);
            String endDateStr = endDate.format(sqlFormatter);
            
            // 获取每日登录次数统计 - 基于login_count字段
            String dailyLoginSql = "SELECT DATE(last_login_time) as date, SUM(login_count) as count " +
                    "FROM students " +
                    "WHERE DATE(last_login_time) BETWEEN ? AND ? " +
                    "GROUP BY DATE(last_login_time) " +
                    "ORDER BY DATE(last_login_time)";
            
            List<Map<String, Object>> dailyResults = jdbcTemplate.queryForList(dailyLoginSql, startDateStr, endDateStr);
            
            // 如果没有按日期的登录数据，则获取总体登录次数
            if (dailyResults.isEmpty()) {
                // 直接统计全部学生的登录次数分布
                String loginCountSql = "SELECT login_count as count, COUNT(*) as student_count FROM students GROUP BY login_count ORDER BY login_count";
                List<Map<String, Object>> loginCountData = jdbcTemplate.queryForList(loginCountSql);
                
                LocalDateTime currentDate = startDate;
                int index = 0;
                while (!currentDate.isAfter(endDate)) {
                    Map<String, Object> stat = new HashMap<>();
                    String formattedDate = currentDate.format(sqlFormatter);
                    stat.put("date", formattedDate);
                    
                    // 如果有数据则使用真实数据，否则使用0
                    if (index < loginCountData.size()) {
                        Map<String, Object> row = loginCountData.get(index);
                        // 添加对student_count字段的null检查
                        Object countObj = row.get("student_count");
                        int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                        stat.put("count", count);
                        index++;
                    } else {
                        stat.put("count", 0);
                    }
                    
                    dailyStats.add(stat);
                    currentDate = currentDate.plusDays(1);
                }
            } else {
                for (Map<String, Object> row : dailyResults) {
                    Map<String, Object> stat = new HashMap<>();
                    // 添加对date字段的null检查
                    Object dateObj = row.get("date");
                    stat.put("date", (dateObj != null) ? dateObj.toString() : "未知日期");
                    
                    // 添加对count字段的null检查
                    Object countObj = row.get("count");
                    int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                    stat.put("count", count);
                    
                    dailyStats.add(stat);
                }
            }
            
            // 查询学生活跃模块分布（基于班级）
            String moduleSql = "SELECT class_name as module, COUNT(*) as count " +
                    "FROM students " +
                    "GROUP BY class_name " +
                    "ORDER BY count DESC";
            
            List<Map<String, Object>> moduleResults = jdbcTemplate.queryForList(moduleSql);
            
            for (Map<String, Object> row : moduleResults) {
                Map<String, Object> stat = new HashMap<>();
                // 如果班级为null，则显示为"未分配班级"
                Object moduleObj = row.get("module");
                String moduleName = (moduleObj != null) ? moduleObj.toString() : "未分配班级";
                stat.put("module", moduleName);
                
                // 添加对count字段的null检查
                Object countObj = row.get("count");
                int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                stat.put("count", count);
                
                moduleStats.add(stat);
            }
            
            data.put("dailyStats", dailyStats);
            data.put("moduleStats", moduleStats);
            
            // 添加学生总数统计
            try {
                String totalStudentSql = "SELECT COUNT(*) as total_count FROM students";
                Map<String, Object> totalResult = jdbcTemplate.queryForMap(totalStudentSql);
                Object totalObj = totalResult.get("total_count");
                int totalStudents = (totalObj != null) ? ((Number) totalObj).intValue() : 0;
                data.put("totalStudents", totalStudents);
                
                // 添加按班级分组的学生数量统计
                String classSql = "SELECT class_name, COUNT(*) as student_count FROM students GROUP BY class_name";
                List<Map<String, Object>> classResults = jdbcTemplate.queryForList(classSql);
                data.put("classStats", classResults);
            } catch (Exception e) {
                logger.error("获取学生总数统计失败: {}", e.getMessage());
                data.put("totalStudents", 0);
                data.put("classStats", new ArrayList<>());
            }
            
            result.put("data", data);
            
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            logger.error("获取学生使用统计数据失败: {}", e.getMessage(), e);
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "获取学生使用统计数据失败: " + e.getMessage());
            return ResponseEntity.badRequest().body(response);
        }
    }

    /**
     * 获取教学效率指数
     */
    @GetMapping("/teaching-efficiency")
    public ResponseEntity<?> getTeachingEfficiencyMetrics(@RequestParam(defaultValue = "week") String period) {
        try {
            logger.info("获取教学效率统计，周期: {}", period);
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            
            Map<String, Object> data = new HashMap<>();
            List<Map<String, Object>> preparationStats = new ArrayList<>();
            List<Map<String, Object>> exerciseStats = new ArrayList<>();
            List<Map<String, Object>> efficiencyTrend = new ArrayList<>();
            
            // 获取日期范围
            LocalDateTime endDate = LocalDateTime.now();
            LocalDateTime startDate;
            
            switch (period) {
                case "week":
                    startDate = endDate.minusWeeks(1);
                    break;
                case "month":
                    startDate = endDate.minusMonths(1);
                    break;
                case "semester":
                    startDate = endDate.minusMonths(6);
                    break;
                default:
                    startDate = endDate.minusWeeks(1);
            }
            
            // 格式化日期为SQL格式
            DateTimeFormatter sqlFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String startDateStr = startDate.format(sqlFormatter);
            String endDateStr = endDate.format(sqlFormatter);
            
            // 查询各科备课与修正耗时
            String prepSql = "SELECT subject, AVG(preparation_time) as prep_time, AVG(revision_time) as rev_time " +
                    "FROM teaching_efficiency_metrics " +
                    "WHERE date BETWEEN ? AND ? " +
                    "GROUP BY subject";
            
            List<Map<String, Object>> prepResults = jdbcTemplate.queryForList(prepSql, startDateStr, endDateStr);
            
            for (Map<String, Object> row : prepResults) {
                Map<String, Object> stat = new HashMap<>();
                Object subjectObj = row.get("subject");
                String subject = (subjectObj != null) ? subjectObj.toString() : "未知科目";
                stat.put("subject", subject);
                
                Object prepTimeObj = row.get("prep_time");
                int prepTime = (prepTimeObj != null) ? ((Number) prepTimeObj).intValue() : 0;
                stat.put("preparationTime", prepTime);
                
                Object revTimeObj = row.get("rev_time");
                int revTime = (revTimeObj != null) ? ((Number) revTimeObj).intValue() : 0;
                stat.put("revisionTime", revTime);
                
                preparationStats.add(stat);
            }
            
            // 如果没有准备时间数据，查询可用科目并填充零值
            if (preparationStats.isEmpty()) {
                // 从教师表中获取科目列表
                String subjectSql = "SELECT DISTINCT subject FROM teachers WHERE subject IS NOT NULL";
                List<Map<String, Object>> subjects = jdbcTemplate.queryForList(subjectSql);
                
                // 如果没有科目数据，添加一个默认"未知科目"项
                if (subjects.isEmpty()) {
                    Map<String, Object> stat = new HashMap<>();
                    stat.put("subject", "未知科目");
                    stat.put("preparationTime", 0);
                    stat.put("revisionTime", 0);
                    preparationStats.add(stat);
                } else {
                    for (Map<String, Object> subject : subjects) {
                        Map<String, Object> stat = new HashMap<>();
                        stat.put("subject", subject.get("subject"));
                        stat.put("preparationTime", 0);
                        stat.put("revisionTime", 0);
                        preparationStats.add(stat);
                    }
                }
            }
            
            // 查询习题设计与修正耗时
            String exerciseSql = "SELECT subject, AVG(exercise_design_time) as design_time, AVG(revision_time) as rev_time " +
                    "FROM teaching_efficiency_metrics " +
                    "WHERE date BETWEEN ? AND ? " +
                    "GROUP BY subject";
            
            List<Map<String, Object>> exerciseResults = jdbcTemplate.queryForList(exerciseSql, startDateStr, endDateStr);
            
            for (Map<String, Object> row : exerciseResults) {
                Map<String, Object> stat = new HashMap<>();
                Object subjectObj = row.get("subject");
                String subject = (subjectObj != null) ? subjectObj.toString() : "未知科目";
                stat.put("subject", subject);
                
                Object designTimeObj = row.get("design_time");
                int designTime = (designTimeObj != null) ? ((Number) designTimeObj).intValue() : 0;
                stat.put("designTime", designTime);
                
                Object revTimeObj = row.get("rev_time");
                int revTime = (revTimeObj != null) ? ((Number) revTimeObj).intValue() : 0;
                stat.put("revisionTime", revTime);
                
                exerciseStats.add(stat);
            }
            
            // 如果没有习题设计数据，复用已获取的科目列表
            if (exerciseStats.isEmpty() && !preparationStats.isEmpty()) {
                for (Map<String, Object> prepStat : preparationStats) {
                    Map<String, Object> stat = new HashMap<>();
                    stat.put("subject", prepStat.get("subject"));
                    stat.put("designTime", 0);
                    stat.put("revisionTime", 0);
                    exerciseStats.add(stat);
                }
            } else if (exerciseStats.isEmpty()) {
                // 如果preparationStats也是空的，从教师表中获取科目
                String subjectSql = "SELECT DISTINCT subject FROM teachers WHERE subject IS NOT NULL";
                List<Map<String, Object>> subjects = jdbcTemplate.queryForList(subjectSql);
                
                if (subjects.isEmpty()) {
                    Map<String, Object> stat = new HashMap<>();
                    stat.put("subject", "未知科目");
                    stat.put("designTime", 0);
                    stat.put("revisionTime", 0);
                    exerciseStats.add(stat);
                } else {
                    for (Map<String, Object> subject : subjects) {
                        Map<String, Object> stat = new HashMap<>();
                        stat.put("subject", subject.get("subject"));
                        stat.put("designTime", 0);
                        stat.put("revisionTime", 0);
                        exerciseStats.add(stat);
                    }
                }
            }
            
            // 查询效率趋势（按日期的效率指数）
            String trendSql = "SELECT date, AVG(efficiency_index) as index_value " +
                    "FROM teaching_efficiency_metrics " +
                    "WHERE date BETWEEN ? AND ? " +
                    "GROUP BY date " +
                    "ORDER BY date";
            
            List<Map<String, Object>> trendResults = jdbcTemplate.queryForList(trendSql, startDateStr, endDateStr);
            
            for (Map<String, Object> row : trendResults) {
                Map<String, Object> trend = new HashMap<>();
                trend.put("date", row.get("date").toString());
                
                Object indexValueObj = row.get("index_value");
                double indexValue = (indexValueObj != null) ? ((Number) indexValueObj).doubleValue() : 0.0;
                trend.put("index", indexValue);
                
                efficiencyTrend.add(trend);
            }
            
            // 如果没有效率指数数据，生成日期范围内的零值数据点
            if (efficiencyTrend.isEmpty()) {
                LocalDateTime current = startDate;
                while (!current.isAfter(endDate)) {
                    Map<String, Object> trend = new HashMap<>();
                    trend.put("date", current.format(sqlFormatter));
                    trend.put("index", 0.0);
                    efficiencyTrend.add(trend);
                    current = current.plusDays(1);
                }
            }
            
            data.put("preparationStats", preparationStats);
            data.put("exerciseStats", exerciseStats);
            data.put("efficiencyTrend", efficiencyTrend);
            
            result.put("data", data);
            
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            logger.error("获取教学效率统计数据失败: {}", e.getMessage(), e);
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "获取教学效率统计数据失败: " + e.getMessage());
            return ResponseEntity.badRequest().body(response);
        }
    }
    
    /**
     * 获取学生学习情况统计数据
     */
    @GetMapping("/student-learning")
    public ResponseEntity<?> getStudentLearningMetrics(@RequestParam(defaultValue = "week") String period,
                                                     @RequestParam(required = false) String classId) {
        try {
            logger.info("获取学生学习情况统计，周期: {}, 班级: {}", period, classId);
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            
            Map<String, Object> data = new HashMap<>();
            List<Map<String, Object>> correctRateTrend = new ArrayList<>();
            List<Map<String, Object>> knowledgeMastery = new ArrayList<>();
            List<Map<String, Object>> highErrorPoints = new ArrayList<>();
            
            // 首先获取班级名称
            String className = null;
            if (classId != null && !classId.trim().isEmpty()) {
                try {
                    String classNameSql = "SELECT class_name FROM classes WHERE id = ?";
                    className = jdbcTemplate.queryForObject(classNameSql, String.class, classId);
                    logger.info("获取到班级名称: {}", className);
                } catch (Exception e) {
                    logger.warn("通过ID获取班级名称失败: {}，将直接使用传入的classId作为班级名称", e.getMessage());
                    className = classId; // 如果查询失败，就直接使用传入的classId作为班级名称
                }
            }
            
            // 获取日期范围
            LocalDateTime endDate = LocalDateTime.now();
            LocalDateTime startDate;
            
            switch (period) {
                case "week":
                    startDate = endDate.minusWeeks(1);
                    break;
                case "month":
                    startDate = endDate.minusMonths(1);
                    break;
                case "semester":
                    startDate = endDate.minusMonths(6);
                    break;
                case "year":
                    startDate = endDate.minusYears(1);
                    break;
                default:
                    startDate = endDate.minusWeeks(1);
            }
            
            // 格式化日期为SQL格式
            DateTimeFormatter sqlFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String startDateStr = startDate.format(sqlFormatter);
            String endDateStr = endDate.format(sqlFormatter);
            
            // 构建基础查询
            String baseQuery = "SELECT sps.* FROM student_practice_sessions sps ";
            List<Object> baseParams = new ArrayList<>();
            
            if (className != null && !className.trim().isEmpty()) {
                baseQuery += "JOIN students s ON sps.student_id = s.student_id AND s.class_name = ? ";
                baseParams.add(className);
            }
            
            baseQuery += "WHERE sps.start_time BETWEEN ? AND ? ";
            baseParams.add(startDateStr);
            baseParams.add(endDateStr);
            
            // 查询正确率趋势
            String correctRateSql = "SELECT DATE(sps.start_time) as date, " +
                    "CASE WHEN SUM(sps.question_count) > 0 THEN AVG(sps.correct_count*100.0/NULLIF(sps.question_count, 0)) ELSE 0 END as rate " +
                    "FROM (" + baseQuery + ") AS sps " +
                    "GROUP BY DATE(sps.start_time) " +
                    "ORDER BY date";
            
            List<Map<String, Object>> rateResults;
            try {
                rateResults = jdbcTemplate.queryForList(correctRateSql, baseParams.toArray());
            } catch (Exception e) {
                logger.error("查询正确率趋势失败: {}", e.getMessage());
                // 创建空列表而不是抛出异常
                rateResults = new ArrayList<>();
            }
            
            for (Map<String, Object> row : rateResults) {
                Map<String, Object> rate = new HashMap<>();
                rate.put("date", row.get("date").toString());
                
                Object rateObj = row.get("rate");
                double rateValue = (rateObj != null) ? ((Number) rateObj).doubleValue() : 0.0;
                rate.put("rate", rateValue);
                
                correctRateTrend.add(rate);
            }
            
            // 如果没有正确率数据，生成日期范围内的零值数据点
            if (correctRateTrend.isEmpty()) {
                LocalDateTime current = startDate;
                while (!current.isAfter(endDate)) {
                    Map<String, Object> rate = new HashMap<>();
                    rate.put("date", current.format(sqlFormatter));
                    rate.put("rate", 0.0);
                    correctRateTrend.add(rate);
                    current = current.plusDays(1);
                }
            }
            
            // 查询知识点掌握情况
            String knowledgeSql = "SELECT sps.topic as knowledge_point, " +
                    "CASE WHEN SUM(sps.question_count) > 0 THEN AVG(sps.correct_count*100.0/NULLIF(sps.question_count, 0)) ELSE 0 END as level " +
                    "FROM (" + baseQuery + ") AS sps " +
                    "WHERE sps.topic IS NOT NULL " +
                    "GROUP BY sps.topic " +
                    "ORDER BY level DESC";
            
            List<Map<String, Object>> knowledgeResults;
            try {
                knowledgeResults = jdbcTemplate.queryForList(knowledgeSql, baseParams.toArray());
            } catch (Exception e) {
                logger.error("查询知识点掌握情况失败: {}", e.getMessage());
                knowledgeResults = new ArrayList<>();
            }
            
            for (Map<String, Object> row : knowledgeResults) {
                Map<String, Object> mastery = new HashMap<>();
                
                Object pointObj = row.get("knowledge_point");
                String point = (pointObj != null) ? pointObj.toString() : "未知知识点";
                mastery.put("point", point);
                
                Object levelObj = row.get("level");
                double level = (levelObj != null) ? ((Number) levelObj).doubleValue() : 0.0;
                mastery.put("level", level);
                
                knowledgeMastery.add(mastery);
            }
            
            // 如果没有知识点掌握数据，查询常见知识点列表
            if (knowledgeMastery.isEmpty()) {
                // 尝试从知识点表中获取常见知识点
                String pointsSql = "SELECT DISTINCT name FROM knowledge_points LIMIT 10";
                try {
                    List<Map<String, Object>> points = jdbcTemplate.queryForList(pointsSql);
                    
                    if (!points.isEmpty()) {
                        for (Map<String, Object> point : points) {
                            Map<String, Object> mastery = new HashMap<>();
                            Object nameObj = point.get("name");
                            String name = (nameObj != null) ? nameObj.toString() : "知识点";
                            mastery.put("point", name);
                            mastery.put("level", 0.0);
                            knowledgeMastery.add(mastery);
                        }
                    } else {
                        // 如果知识点表为空，添加一个默认项
                        Map<String, Object> mastery = new HashMap<>();
                        mastery.put("point", "暂无知识点数据");
                        mastery.put("level", 0.0);
                        knowledgeMastery.add(mastery);
                    }
                } catch (Exception e) {
                    // 如果查询知识点表失败，添加一个默认项
                    logger.warn("查询知识点表失败: {}", e.getMessage());
                    Map<String, Object> mastery = new HashMap<>();
                    mastery.put("point", "暂无知识点数据");
                    mastery.put("level", 0.0);
                    knowledgeMastery.add(mastery);
                }
            }
            
            // 查询高频错误点
            String errorSql = "SELECT sps.topic as error_point, SUM(sps.question_count - sps.correct_count) as frequency " +
                    "FROM (" + baseQuery + ") AS sps " +
                    "WHERE sps.topic IS NOT NULL " +
                    "GROUP BY sps.topic " +
                    "HAVING SUM(sps.question_count - sps.correct_count) > 0 " +
                    "ORDER BY frequency DESC " +
                    "LIMIT 5";
            
            List<Map<String, Object>> errorResults;
            try {
                errorResults = jdbcTemplate.queryForList(errorSql, baseParams.toArray());
            } catch (Exception e) {
                logger.error("查询高频错误点失败: {}", e.getMessage());
                errorResults = new ArrayList<>();
            }
            
            for (Map<String, Object> row : errorResults) {
                Map<String, Object> error = new HashMap<>();
                
                Object pointObj = row.get("error_point");
                String point = (pointObj != null) ? pointObj.toString() : "未知错误点";
                error.put("point", point);
                
                Object freqObj = row.get("frequency");
                int frequency = (freqObj != null) ? ((Number) freqObj).intValue() : 0;
                error.put("frequency", frequency);
                
                highErrorPoints.add(error);
            }
            
            // 如果没有错误点数据，添加一个默认项
            if (highErrorPoints.isEmpty()) {
                Map<String, Object> error = new HashMap<>();
                error.put("point", "暂无错误点数据");
                error.put("frequency", 0);
                highErrorPoints.add(error);
            }
            
            data.put("correctRateTrend", correctRateTrend);
            data.put("knowledgeMastery", knowledgeMastery);
            data.put("highErrorPoints", highErrorPoints);
            
            result.put("data", data);
            
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            logger.error("获取学生学习情况统计数据失败: {}", e.getMessage(), e);
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "获取学生学习情况统计数据失败: " + e.getMessage());
            return ResponseEntity.badRequest().body(response);
        }
    }

    /**
     * 获取学生学习进度统计（包括学生排名和学科比较数据）
     */
    @GetMapping("/learning-progress")
    public ResponseEntity<?> getStudentLearningProgress(@RequestParam(defaultValue = "week") String period,
                                                      @RequestParam(required = false) String classId) {
        try {
            logger.info("获取学生学习进度统计，周期: {}, 班级: {}", period, classId);
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            
            Map<String, Object> data = new HashMap<>();
            List<Map<String, Object>> studentRanking = new ArrayList<>();
            List<Map<String, Object>> subjectStats = new ArrayList<>();
            
            // 首先获取班级名称
            String className = null;
            if (classId != null && !classId.trim().isEmpty()) {
                try {
                    String classNameSql = "SELECT class_name FROM classes WHERE id = ?";
                    className = jdbcTemplate.queryForObject(classNameSql, String.class, classId);
                    logger.info("获取到班级名称: {}", className);
                } catch (Exception e) {
                    logger.warn("通过ID获取班级名称失败: {}，将直接使用传入的classId作为班级名称", e.getMessage());
                    className = classId; // 如果查询失败，就直接使用传入的classId作为班级名称
                }
            }
            
            // 获取日期范围
            LocalDateTime endDate = LocalDateTime.now();
            LocalDateTime startDate;
            
            switch (period) {
                case "week":
                    startDate = endDate.minusWeeks(1);
                    break;
                case "month":
                    startDate = endDate.minusMonths(1);
                    break;
                case "semester":
                    startDate = endDate.minusMonths(6);
                    break;
                default:
                    startDate = endDate.minusWeeks(1);
            }
            
            // 格式化日期为SQL格式
            DateTimeFormatter sqlFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String startDateStr = startDate.format(sqlFormatter);
            String endDateStr = endDate.format(sqlFormatter);
            
            // 查询学生排名数据 - 合并所有学科数据
            String rankingSql = "SELECT s.student_id, s.name, " +
                    "COUNT(sps.id) as session_count, " +
                    "SUM(sps.question_count) as total_exercises, " +
                    "SUM(sps.correct_count) as correct_exercises, " +
                    "CASE WHEN SUM(sps.question_count) > 0 THEN SUM(sps.correct_count)*100.0/SUM(sps.question_count) ELSE 0 END as accuracy " +
                    "FROM students s " +
                    "LEFT JOIN student_practice_sessions sps ON s.student_id = sps.student_id " +
                    "AND sps.start_time BETWEEN ? AND ? ";
            
            List<Object> params = new ArrayList<>();
            params.add(startDateStr);
            params.add(endDateStr);
            
            if (className != null && !className.trim().isEmpty()) {
                rankingSql += "WHERE s.class_name = ? ";
                params.add(className);
            }
            
            rankingSql += "GROUP BY s.student_id, s.name " +
                    "ORDER BY accuracy DESC, total_exercises DESC " +
                    "LIMIT 20";
            
            List<Map<String, Object>> rankingResults;
            try {
                rankingResults = jdbcTemplate.queryForList(rankingSql, params.toArray());
            } catch (Exception e) {
                logger.error("查询学生排名数据失败: {}", e.getMessage());
                rankingResults = new ArrayList<>();
            }
            
            for (Map<String, Object> row : rankingResults) {
                Map<String, Object> ranking = new HashMap<>();
                
                Object studentIdObj = row.get("student_id");
                String studentId = (studentIdObj != null) ? studentIdObj.toString() : "";
                ranking.put("studentId", studentId);
                
                Object nameObj = row.get("name");
                String name = (nameObj != null) ? nameObj.toString() : "未知学生";
                ranking.put("name", name);
                
                Object sessionCountObj = row.get("session_count");
                int sessionCount = (sessionCountObj != null) ? ((Number) sessionCountObj).intValue() : 0;
                ranking.put("sessionCount", sessionCount);
                
                Object totalObj = row.get("total_exercises");
                int totalExercises = (totalObj != null) ? ((Number) totalObj).intValue() : 0;
                ranking.put("totalExercises", totalExercises);
                
                Object correctObj = row.get("correct_exercises");
                int correctExercises = (correctObj != null) ? ((Number) correctObj).intValue() : 0;
                ranking.put("correctExercises", correctExercises);
                
                Object accuracyObj = row.get("accuracy");
                double accuracy = (accuracyObj != null) ? ((Number) accuracyObj).doubleValue() : 0.0;
                ranking.put("accuracy", accuracy);
                
                studentRanking.add(ranking);
            }
            
            // 查询学科统计数据
            String subjectSql = "SELECT sps.subject, " +
                    "COUNT(DISTINCT sps.student_id) as student_count, " +
                    "COUNT(sps.id) as session_count, " +
                    "SUM(sps.question_count) as exercise_count, " +
                    "CASE WHEN SUM(sps.question_count) > 0 THEN SUM(sps.correct_count)*100.0/SUM(sps.question_count) ELSE 0 END as accuracy " +
                    "FROM student_practice_sessions sps ";
            
            if (className != null && !className.trim().isEmpty()) {
                subjectSql += "JOIN students s ON sps.student_id = s.student_id AND s.class_name = ? ";
            }
            
            subjectSql += "WHERE sps.start_time BETWEEN ? AND ? ";
            
            List<Object> subjectParams = new ArrayList<>();
            if (className != null && !className.trim().isEmpty()) {
                subjectParams.add(className);
            }
            subjectParams.add(startDateStr);
            subjectParams.add(endDateStr);
            
            subjectSql += "GROUP BY sps.subject ORDER BY accuracy DESC";
            
            List<Map<String, Object>> subjectResults;
            try {
                subjectResults = jdbcTemplate.queryForList(subjectSql, subjectParams.toArray());
            } catch (Exception e) {
                logger.error("查询学科统计数据失败: {}", e.getMessage());
                subjectResults = new ArrayList<>();
            }
            
            for (Map<String, Object> row : subjectResults) {
                Map<String, Object> subjectStat = new HashMap<>();
                
                Object subjectObj = row.get("subject");
                String subjectName = (subjectObj != null) ? subjectObj.toString() : "未知学科";
                subjectStat.put("subject", subjectName);
                
                Object studentCountObj = row.get("student_count");
                int studentCount = (studentCountObj != null) ? ((Number) studentCountObj).intValue() : 0;
                subjectStat.put("studentCount", studentCount);
                
                Object sessionCountObj = row.get("session_count");
                int sessionCount = (sessionCountObj != null) ? ((Number) sessionCountObj).intValue() : 0;
                subjectStat.put("sessionCount", sessionCount);
                
                Object exerciseCountObj = row.get("exercise_count");
                int exerciseCount = (exerciseCountObj != null) ? ((Number) exerciseCountObj).intValue() : 0;
                subjectStat.put("exerciseCount", exerciseCount);
                
                Object accuracyObj = row.get("accuracy");
                double accuracy = (accuracyObj != null) ? ((Number) accuracyObj).doubleValue() : 0.0;
                subjectStat.put("accuracy", accuracy);
                
                subjectStats.add(subjectStat);
            }
            
            // 添加一个合计统计项
            if (!subjectStats.isEmpty()) {
                Map<String, Object> totalStat = new HashMap<>();
                totalStat.put("subject", "全部科目");
                
                int totalStudentCount = 0;
                int totalSessionCount = 0;
                int totalExerciseCount = 0;
                double weightedAccuracy = 0;
                int totalWeight = 0;
                
                for (Map<String, Object> stat : subjectStats) {
                    totalStudentCount += (int) stat.get("studentCount");
                    totalSessionCount += (int) stat.get("sessionCount");
                    int exerciseCount = (int) stat.get("exerciseCount");
                    totalExerciseCount += exerciseCount;
                    
                    // 计算加权平均正确率
                    if (exerciseCount > 0) {
                        weightedAccuracy += (double) stat.get("accuracy") * exerciseCount;
                        totalWeight += exerciseCount;
                    }
                }
                
                // 获取班级实际学生总数
                if (className != null && !className.trim().isEmpty()) {
                    String countSql = "SELECT COUNT(*) FROM students WHERE class_name = ?";
                    Integer actualStudentCount = jdbcTemplate.queryForObject(countSql, Integer.class, className);
                    
                    if (actualStudentCount != null) {
                        totalStudentCount = actualStudentCount;
                    }
                }
                
                totalStat.put("studentCount", totalStudentCount);
                totalStat.put("sessionCount", totalSessionCount);
                totalStat.put("exerciseCount", totalExerciseCount);
                
                // 计算加权平均正确率
                double finalAccuracy = totalWeight > 0 ? weightedAccuracy / totalWeight : 0.0;
                totalStat.put("accuracy", finalAccuracy);
                
                // 将合计放在首位
                subjectStats.add(0, totalStat);
            } else {
                // 如果没有任何学科统计数据，添加一个只有学生总数的默认项
                Map<String, Object> totalStat = new HashMap<>();
                totalStat.put("subject", "全部科目");
                
                // 获取班级实际学生总数
                int totalStudentCount = 0;
                if (className != null && !className.trim().isEmpty()) {
                    String countSql = "SELECT COUNT(*) FROM students WHERE class_name = ?";
                    Integer actualStudentCount = jdbcTemplate.queryForObject(countSql, Integer.class, className);
                    
                    if (actualStudentCount != null) {
                        totalStudentCount = actualStudentCount;
                    }
                }
                
                totalStat.put("studentCount", totalStudentCount);
                totalStat.put("sessionCount", 0);
                totalStat.put("exerciseCount", 0);
                totalStat.put("accuracy", 0.0);
                
                subjectStats.add(totalStat);
            }
            
            data.put("studentRanking", studentRanking);
            data.put("subjectStats", subjectStats);
            result.put("data", data);
            
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            logger.error("获取学生学习进度统计数据失败: {}", e.getMessage(), e);
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "获取学生学习进度统计数据失败: " + e.getMessage());
            return ResponseEntity.badRequest().body(response);
        }
    }

    /**
     * 获取教学资源统计（按学科分类）
     */
    @GetMapping("/teaching-resources")
    public ResponseEntity<?> getTeachingResourcesBySubject(@RequestParam(required = false) String subject) {
        try {
            logger.info("获取教学资源统计，学科: {}", subject);
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            
            List<Map<String, Object>> resourceStats = new ArrayList<>();
            
            // 构建查询
            String resourceSql;
            List<Object> params = new ArrayList<>();
            
            if (subject != null && !subject.trim().isEmpty()) {
                resourceSql = "SELECT subject, type, COUNT(*) as count " +
                        "FROM teaching_resources " +
                        "WHERE subject = ? " +
                        "GROUP BY subject, type " +
                        "ORDER BY count DESC";
                params.add(subject);
            } else {
                resourceSql = "SELECT subject, type, COUNT(*) as count " +
                        "FROM teaching_resources " +
                        "GROUP BY subject, type " +
                        "ORDER BY subject, count DESC";
            }
            
            List<Map<String, Object>> resources = jdbcTemplate.queryForList(resourceSql, params.toArray());
            
            // 处理查询结果
            for (Map<String, Object> row : resources) {
                Map<String, Object> stat = new HashMap<>();
                
                Object subjectObj = row.get("subject");
                String subjectName = (subjectObj != null) ? subjectObj.toString() : "未知学科";
                stat.put("subject", subjectName);
                
                Object typeObj = row.get("type");
                String typeName = (typeObj != null) ? typeObj.toString() : "其他";
                stat.put("type", typeName);
                
                Object countObj = row.get("count");
                int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                stat.put("count", count);
                
                resourceStats.add(stat);
            }
            
            // 如果没有数据，尝试从教师表获取可用学科
            if (resourceStats.isEmpty()) {
                // 查询可用学科
                String subjectSql = "SELECT DISTINCT subject FROM teachers WHERE subject IS NOT NULL";
                List<Map<String, Object>> subjects = jdbcTemplate.queryForList(subjectSql);
                
                String[] resourceTypes = {"课件", "试卷", "教案", "习题"};
                
                // 如果有学科数据，为每个学科创建零值记录
                if (!subjects.isEmpty()) {
                    for (Map<String, Object> subjectRow : subjects) {
                        Object subjectObj = subjectRow.get("subject");
                        String subjectName = (subjectObj != null) ? subjectObj.toString() : "未知学科";
                        
                        // 如果指定了学科查询且不匹配当前学科，则跳过
                        if (subject != null && !subject.trim().isEmpty() && !subject.equals(subjectName)) {
                            continue;
                        }
                        
                        // 为每种资源类型创建零值记录
                        for (String type : resourceTypes) {
                            Map<String, Object> stat = new HashMap<>();
                            stat.put("subject", subjectName);
                            stat.put("type", type);
                            stat.put("count", 0);
                            resourceStats.add(stat);
                        }
                    }
                } else {
                    // 如果没有学科数据，创建一个默认记录
                    Map<String, Object> stat = new HashMap<>();
                    stat.put("subject", subject != null && !subject.trim().isEmpty() ? subject : "未知学科");
                    stat.put("type", "课件");
                    stat.put("count", 0);
                    resourceStats.add(stat);
                }
            }
            
            Map<String, Object> data = new HashMap<>();
            data.put("resources", resourceStats);
            result.put("data", data);
            
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            logger.error("获取教学资源统计数据失败: {}", e.getMessage(), e);
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "获取教学资源统计数据失败: " + e.getMessage());
            return ResponseEntity.badRequest().body(response);
        }
    }
    
    /**
     * 导出教学资源统计数据（CSV格式）
     */
    @GetMapping("/teaching-resources/export")
    public void exportTeachingResources(
            @RequestParam(required = false) String subject,
            HttpServletResponse response) throws IOException {
        logger.info("导出教学资源统计，学科: {}", subject);
        
        try {
            // 设置响应头
            response.setContentType("text/csv");
            response.setCharacterEncoding("UTF-8");
            String filename = "teaching_resources_" + 
                    (subject != null && !subject.isEmpty() ? subject + "_" : "") + 
                    LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddHHmmss")) + 
                    ".csv";
            String encodedFilename = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString()).replaceAll("\\+", "%20");
            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFilename);
            
            // 构建查询
            String resourceSql;
            List<Object> params = new ArrayList<>();
            
            if (subject != null && !subject.trim().isEmpty()) {
                resourceSql = "SELECT subject, type, title, author, created_at, updated_at, download_count " +
                        "FROM teaching_resources " +
                        "WHERE subject = ? " +
                        "ORDER BY subject, type, download_count DESC";
                params.add(subject);
            } else {
                resourceSql = "SELECT subject, type, title, author, created_at, updated_at, download_count " +
                        "FROM teaching_resources " +
                        "ORDER BY subject, type, download_count DESC";
            }
            
            List<Map<String, Object>> resources = jdbcTemplate.queryForList(resourceSql, params.toArray());
            
            // 写入CSV文件
            try (OutputStream os = response.getOutputStream()) {
                // 添加BOM标记，解决Excel打开中文乱码问题
                os.write(new byte[] { (byte)0xEF, (byte)0xBB, (byte)0xBF });
                
                // 写入CSV标题行
                StringBuilder header = new StringBuilder();
                header.append("学科,");
                header.append("资源类型,");
                header.append("资源标题,");
                header.append("作者,");
                header.append("创建日期,");
                header.append("更新日期,");
                header.append("下载次数\n");
                os.write(header.toString().getBytes(StandardCharsets.UTF_8));
                
                // 如果没有资源数据
                if (resources.isEmpty()) {
                    // 获取可用学科列表
                    String subjectSql = "SELECT DISTINCT subject FROM teachers WHERE subject IS NOT NULL";
                    List<Map<String, Object>> subjects = jdbcTemplate.queryForList(subjectSql);
                    
                    StringBuilder emptyNote = new StringBuilder();
                    if (!subjects.isEmpty() && (subject == null || subject.trim().isEmpty())) {
                        // 写入空数据说明
                        for (Map<String, Object> subjectRow : subjects) {
                            Object subjectObj = subjectRow.get("subject");
                            String subjectName = (subjectObj != null) ? subjectObj.toString() : "未知学科";
                            
                            emptyNote.append(csvEscape(subjectName)).append(",");
                            emptyNote.append("暂无资源,");
                            emptyNote.append("无,");
                            emptyNote.append("无,");
                            emptyNote.append(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))).append(",");
                            emptyNote.append(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))).append(",");
                            emptyNote.append("0\n");
                        }
                    } else {
                        // 只写入一行空数据
                        emptyNote.append(csvEscape(subject != null ? subject : "未知学科")).append(",");
                        emptyNote.append("暂无资源,");
                        emptyNote.append("无,");
                        emptyNote.append("无,");
                        emptyNote.append(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))).append(",");
                        emptyNote.append(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))).append(",");
                        emptyNote.append("0\n");
                    }
                    os.write(emptyNote.toString().getBytes(StandardCharsets.UTF_8));
                } else {
                    // 写入实际数据
                    for (Map<String, Object> resource : resources) {
                        StringBuilder line = new StringBuilder();
                        
                        // 学科
                        Object subjectObj = resource.get("subject");
                        String subjectName = (subjectObj != null) ? subjectObj.toString() : "未知学科";
                        line.append(csvEscape(subjectName)).append(",");
                        
                        // 资源类型
                        Object typeObj = resource.get("type");
                        String typeName = (typeObj != null) ? typeObj.toString() : "其他";
                        line.append(csvEscape(typeName)).append(",");
                        
                        // 资源标题
                        Object titleObj = resource.get("title");
                        String title = (titleObj != null) ? titleObj.toString() : "";
                        line.append(csvEscape(title)).append(",");
                        
                        // 作者
                        Object authorObj = resource.get("author");
                        String author = (authorObj != null) ? authorObj.toString() : "";
                        line.append(csvEscape(author)).append(",");
                        
                        // 创建日期
                        Object createdObj = resource.get("created_at");
                        String createdAt = (createdObj != null) ? createdObj.toString() : "";
                        line.append(csvEscape(createdAt)).append(",");
                        
                        // 更新日期
                        Object updatedObj = resource.get("updated_at");
                        String updatedAt = (updatedObj != null) ? updatedObj.toString() : "";
                        line.append(csvEscape(updatedAt)).append(",");
                        
                        // 下载次数
                        Object countObj = resource.get("download_count");
                        int count = (countObj != null) ? ((Number) countObj).intValue() : 0;
                        line.append(count).append("\n");
                        
                        os.write(line.toString().getBytes(StandardCharsets.UTF_8));
                    }
                }
                
                os.flush();
            }
            
        } catch (Exception e) {
            logger.error("导出教学资源统计数据失败: {}", e.getMessage(), e);
            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
            response.getWriter().write("导出失败: " + e.getMessage());
        }
    }
    
    /**
     * 处理CSV字段，确保包含逗号等特殊字符的字段用引号包围
     */
    private String csvEscape(Object obj) {
        if (obj == null) {
            return "";
        }
        String str = obj.toString();
        if (str.contains(",") || str.contains("\"") || str.contains("\n")) {
            return "\"" + str.replace("\"", "\"\"") + "\"";
        }
        return str;
    }

    /**
     * 获取教学效率统计，按教师分组
     */
    @GetMapping("/teacher-efficiency-data")
    public ResponseEntity<?> getTeacherEfficiencyData() {
        try {
            logger.info("获取教师教学效率数据");
            
            Map<String, Object> result = new HashMap<>();
            result.put("success", true);
            
            Map<String, Object> data = new HashMap<>();
            List<Map<String, Object>> efficiencyData = new ArrayList<>();
            List<Map<String, Object>> optimizationData = new ArrayList<>();
            Map<String, List<Double>> teacherTrends = new HashMap<>();
            
            // 直接从teaching_efficiency_metrics表获取数据 - 修复SQL错误
            String sqlEfficiency = 
                "SELECT t.id as teacher_id, t.name as teacher_name, t.subject as department, " +
                "AVG(tem.preparation_time + tem.revision_time) as prep_time, " +
                "AVG(tem.exercise_design_time + tem.exercise_revision_time) as exercise_time, " +
                "AVG(tem.efficiency_index) as efficiency, " +
                "tem.optimization_focus " +
                "FROM teachers t " +
                "JOIN teaching_efficiency_metrics tem ON t.id = tem.teacher_id " +
                "GROUP BY t.id, t.name, t.subject, tem.optimization_focus " +
                "ORDER BY efficiency DESC";
            
            List<Map<String, Object>> results = jdbcTemplate.queryForList(sqlEfficiency);
            
            if (!results.isEmpty()) {
                for (Map<String, Object> row : results) {
                    Map<String, Object> teacherData = new HashMap<>();
                    
                    Object teacherNameObj = row.get("teacher_name");
                    String teacherName = (teacherNameObj != null) ? teacherNameObj.toString() : "未知教师";
                    teacherData.put("teacherName", teacherName);
                    
                    Object departmentObj = row.get("department");
                    String dept = (departmentObj != null) ? departmentObj.toString() : "未知部门";
                    teacherData.put("department", dept);
                    
                    Object prepTimeObj = row.get("prep_time");
                    int prepTime = (prepTimeObj != null) ? ((Number) prepTimeObj).intValue() : 0;
                    teacherData.put("prepTime", prepTime);
                    
                    Object exerciseTimeObj = row.get("exercise_time");
                    int exerciseTime = (exerciseTimeObj != null) ? ((Number) exerciseTimeObj).intValue() : 0;
                    teacherData.put("exerciseTime", exerciseTime);
                    
                    Object efficiencyObj = row.get("efficiency");
                    double efficiency = (efficiencyObj != null) ? ((Number) efficiencyObj).doubleValue() : 0.0;
                    teacherData.put("efficiency", efficiency);
                    
                    Object optimizationFocusObj = row.get("optimization_focus");
                    String optimizationFocus = (optimizationFocusObj != null) ? optimizationFocusObj.toString() : "无优化建议";
                    teacherData.put("optimizationFocus", optimizationFocus);
                    
                    efficiencyData.add(teacherData);
                    
                    // 收集优化方向数据
                    if (optimizationFocus != null && !optimizationFocus.isEmpty() && !optimizationFocus.equals("无优化建议")) {
                        boolean found = false;
                        for (Map<String, Object> opt : optimizationData) {
                            if (opt.get("name").equals(optimizationFocus)) {
                                int value = (int) opt.get("value");
                                opt.put("value", value + 1);
                                found = true;
                                break;
                            }
                        }
                        
                        if (!found) {
                            Map<String, Object> optData = new HashMap<>();
                            optData.put("name", optimizationFocus);
                            optData.put("value", 1);
                            optimizationData.add(optData);
                        }
                    }
                }
                
                // 获取教师效率趋势数据
                for (Map<String, Object> teacher : efficiencyData) {
                    String teacherName = (String) teacher.get("teacherName");
                    Object teacherId = null;
                    
                    // 查找对应的teacher_id
                    for (Map<String, Object> row : results) {
                        if (teacherName.equals(row.get("teacher_name"))) {
                            teacherId = row.get("teacher_id");
                            break;
                        }
                    }
                    
                    if (teacherId != null) {
                        String trendSql = "SELECT date, efficiency_index FROM teaching_efficiency_metrics " +
                                "WHERE teacher_id = ? " +
                                "ORDER BY date DESC LIMIT 6";
                        
                        List<Map<String, Object>> trendResults = jdbcTemplate.queryForList(trendSql, teacherId);
                        List<Double> trends = new ArrayList<>();
                        
                        if (!trendResults.isEmpty()) {
                            for (Map<String, Object> trend : trendResults) {
                                Object indexObj = trend.get("efficiency_index");
                                double index = (indexObj != null) ? ((Number) indexObj).doubleValue() : 0.0;
                                trends.add(index);
                            }
                            // 反转列表使时间顺序正确
                            Collections.reverse(trends);
                        } else {
                            // 如果没有趋势数据，填充0
                            for (int i = 0; i < 6; i++) {
                                trends.add(0.0);
                            }
                        }
                        
                        teacherTrends.put(teacherName, trends);
                    }
                }
            } else {
                // 如果没有数据，使用模拟数据
                useModelData(efficiencyData, optimizationData, teacherTrends);
            }
            
            data.put("efficiencyData", efficiencyData);
            data.put("optimizationData", optimizationData);
            data.put("teacherTrends", teacherTrends);
            
            result.put("data", data);
            
            return ResponseEntity.ok(result);
        } catch (Exception e) {
            logger.error("获取教师教学效率数据失败: {}", e.getMessage(), e);
            Map<String, Object> response = new HashMap<>();
            response.put("success", false);
            response.put("message", "获取教师教学效率数据失败: " + e.getMessage());
            return ResponseEntity.badRequest().body(response);
        }
    }
    
    /**
     * 使用模拟数据填充教师效率数据集合
     */
    private void useModelData(List<Map<String, Object>> efficiencyData, 
                             List<Map<String, Object>> optimizationData,
                             Map<String, List<Double>> teacherTrends) {
        // 模拟教师效率数据
        String[] teacherNames = {"张老师", "李老师", "王老师", "陈老师", "刘老师"};
        String[] departments = {"语文教研组", "数学教研组", "英语教研组", "理科教研组", "文科教研组"};
        int[] prepTimes = {85, 75, 95, 65, 90};
        int[] exerciseTimes = {60, 45, 70, 55, 65};
        double[] efficiencies = {88, 92, 75, 85, 78};
        String[] optimizations = {"优化备课流程", "增加课堂互动", "简化教案结构", "调整练习难度", "聚焦关键知识点"};
        
        for (int i = 0; i < teacherNames.length; i++) {
            Map<String, Object> teacher = new HashMap<>();
            teacher.put("teacherName", teacherNames[i]);
            teacher.put("department", departments[i]);
            teacher.put("prepTime", prepTimes[i]);
            teacher.put("exerciseTime", exerciseTimes[i]);
            teacher.put("efficiency", efficiencies[i]);
            teacher.put("optimizationFocus", optimizations[i]);
            
            efficiencyData.add(teacher);
            
            // 添加优化方向数据
            Map<String, Object> optData = new HashMap<>();
            optData.put("name", optimizations[i]);
            optData.put("value", 1);
            optimizationData.add(optData);
            
            // 添加趋势数据
            List<Double> trends = new ArrayList<>();
            double baseEfficiency = efficiencies[i] - 10; // 起始效率值较低
            for (int j = 0; j < 6; j++) {
                // 模拟效率逐渐提高的趋势
                trends.add(baseEfficiency + j * 2);
            }
            teacherTrends.put(teacherNames[i], trends);
        }
    }
} 